﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using BLL;
using DAL;

namespace BLL
{
    public class GiaoVienBUO:IBUO<tbl_GiaoVien>
    {
        CSDLQLDiemHSC3DataContext context = new CSDLQLDiemHSC3DataContext();

        public bool Insert(tbl_GiaoVien obj)
        {
            try
            {
                context.tbl_GiaoViens.InsertOnSubmit(obj);
                context.SubmitChanges();
                return true;
            }
            catch
            {
                return false;
            }
        }

        public bool Delete(string id)
        {
            try
            {
                tbl_GiaoVien obj = context.tbl_GiaoViens.Where(p => p.id_GiaoVien.Equals(id)).FirstOrDefault();
                context.tbl_GiaoViens.DeleteOnSubmit(obj);
                context.SubmitChanges();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public bool Delete(tbl_GiaoVien obj)
        {
            throw new NotImplementedException();
        }

        public List<tbl_GiaoVien> GetAll()
        {
            throw new NotImplementedException();
        }

        public tbl_GiaoVien GetSingle(string id)
        {
            return context.tbl_GiaoViens.Where(p => p.id_GiaoVien.Equals(id)).FirstOrDefault();
        }

        public bool Update(string id, tbl_GiaoVien obj)
        {
            tbl_GiaoVien gv = context.tbl_GiaoViens.Where(p => p.id_GiaoVien.Trim() == id.Trim()).FirstOrDefault();
            gv.id_MonHoc = obj.id_MonHoc;
            gv.HoTenGV = obj.HoTenGV;
            gv.NgaySinh = obj.NgaySinh;
            gv.DiaChi = obj.DiaChi;
            gv.SDT = obj.SDT;
            gv.Email = obj.Email;

            context.SubmitChanges();
            return true;
        }

        public String getKhoi(String id)
        {
            return context.tbl_Khois.Where(p => p.id_Khoi.Equals(id)).Select(m => m.TenKhoi).FirstOrDefault();
        }

        public IQueryable HienThiGiaoVien()
        {
            var list = context.tbl_GiaoViens.Join(
                    context.tbl_MonHocs,
                    a => a.id_MonHoc,
                    b => b.id_MonHoc,
                    (a, b) => new
                    {
                        id_MonHoc = a.id_MonHoc,
                        id_Khoi = b.id_Khoi,
                        id_GiaoVien = a.id_GiaoVien,
                        HoTenGV = a.HoTenGV,
                        NgaySinh = a.NgaySinh,
                        DiaChi = a.DiaChi,
                        SDT = a.SDT,
                        Email = a.Email,
                        TenMonHoc = b.TenMonHoc
                    }
                ).Join(
                    context.tbl_Khois,
                    c => c.id_Khoi,
                    d => d.id_Khoi,
                    (c, d) => new {
                        id_MonHoc = c.id_MonHoc,
                        id_Khoi = c.id_Khoi,
                        id_GiaoVien = c.id_GiaoVien,
                        HoTenGV = c.HoTenGV,
                        NgaySinh = c.NgaySinh,
                        DiaChi = c.DiaChi,
                        SDT = c.SDT,
                        Email = c.Email,
                        TenMonHoc = c.TenMonHoc,
                        TenKhoi = d.TenKhoi
                    }
                );

            return list;
        }

        /// <summary>
        /// Lấy tất cả các khối
        /// </summary>
        /// <returns></returns>
        public List<tbl_Khoi> getKhoi()
        {
            return context.tbl_Khois.ToList();
        }

        /// <summary>
        /// Lấy tất cả các môn học
        /// </summary>
        /// <param name="id_khoi"></param>
        /// <returns></returns>
        public List<tbl_MonHoc> getMonDay(String id_khoi)
        {
            return context.tbl_MonHocs.Where(p=>p.id_Khoi.Equals(id_khoi)).ToList();
        }

        /// <summary>
        /// Tìm theo tên giáo viên
        /// </summary>
        /// <param name="tengv"></param>
        /// <returns></returns>
        public IQueryable TimTheoTenGiaoVien(String tengv)
        {
            var list = context.tbl_GiaoViens.Join(
                    context.tbl_MonHocs,
                    a => a.id_MonHoc,
                    b => b.id_MonHoc,
                    (a, b) => new
                    {
                        id_MonHoc = a.id_MonHoc,
                        id_Khoi = b.id_Khoi,
                        id_GiaoVien = a.id_GiaoVien,
                        HoTenGV = a.HoTenGV,
                        NgaySinh = a.NgaySinh,
                        DiaChi = a.DiaChi,
                        SDT = a.SDT,
                        Email = a.Email,
                        TenMonHoc = b.TenMonHoc
                    }
                ).Join(
                    context.tbl_Khois,
                    c => c.id_Khoi,
                    d => d.id_Khoi,
                    (c, d) => new
                    {
                        id_MonHoc = c.id_MonHoc,
                        id_Khoi = c.id_Khoi,
                        id_GiaoVien = c.id_GiaoVien,
                        HoTenGV = c.HoTenGV,
                        NgaySinh = c.NgaySinh,
                        DiaChi = c.DiaChi,
                        SDT = c.SDT,
                        Email = c.Email,
                        TenMonHoc = c.TenMonHoc,
                        TenKhoi = d.TenKhoi
                    }
                ).Where(p => p.HoTenGV.Contains(tengv.Trim()));

            return list;
        }

        /// <summary>
        /// Tìm theo mã giáo viên
        /// </summary>
        /// <param name="magv"></param>
        /// <returns></returns>
        public IQueryable TimTheoMaGiaoVien(String magv)
        {
            var list = context.tbl_GiaoViens.Join(
                    context.tbl_MonHocs,
                    a => a.id_MonHoc,
                    b => b.id_MonHoc,
                    (a, b) => new
                    {
                        id_MonHoc = a.id_MonHoc,
                        id_Khoi = b.id_Khoi,
                        id_GiaoVien = a.id_GiaoVien,
                        HoTenGV = a.HoTenGV,
                        NgaySinh = a.NgaySinh,
                        DiaChi = a.DiaChi,
                        SDT = a.SDT,
                        Email = a.Email,
                        TenMonHoc = b.TenMonHoc
                    }
                ).Join(
                    context.tbl_Khois,
                    c => c.id_Khoi,
                    d => d.id_Khoi,
                    (c, d) => new
                    {
                        id_MonHoc = c.id_MonHoc,
                        id_Khoi = c.id_Khoi,
                        id_GiaoVien = c.id_GiaoVien,
                        HoTenGV = c.HoTenGV,
                        NgaySinh = c.NgaySinh,
                        DiaChi = c.DiaChi,
                        SDT = c.SDT,
                        Email = c.Email,
                        TenMonHoc = c.TenMonHoc,
                        TenKhoi = d.TenKhoi
                    }
                ).Where(p => p.id_GiaoVien.Contains(magv.Trim()));

            return list;
        }

        public String getIDMonHoc(String idKhoi, String mon)
        {
            return context.tbl_MonHocs.Where(p => (p.id_Khoi.Equals(idKhoi)) && (p.TenMonHoc.Equals(mon))).Select(m => m.id_MonHoc).FirstOrDefault();
        }
    }
}
